using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
using APJ.Util;
using System.Globalization;

namespace MTRC.ODMS.Web.SOM.CodeSetup
{
    /// <summary>
    /// Author       : NingHeming
    /// Begin Date   :2008-10-23,   huangyao modified at date: 2008-12-11, NHM made this function bullshit
    /// Description  :Upload Contractor Snapshot  
    /// Modify by zengshunbo 2008-12-11 actionlog(0709)
    /// </summary>
    public partial class ContractorSnapshotUpload : BasePage_SOM
    {
        #region MenuID

        protected override string MenuID
        {
            get
            {
                return "M140818";//set the Menu
            }
        }

        #endregion

        #region Page Init And Load

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            this.ChangeLanguage += new ChangeLanguageEventHandler(SetLanguage_Click);
        }

        #endregion

        #region page load and set the page lanuage

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                SetLanguage();
            }
        }

        /// <summary>
        /// set the lanuage for the page
        /// </summary>
        private void SetLanguage()
        {
            this.lblTitle.Text = this.GetLabel("SOM_CQAContractorSnapshotUpload");
            //this.lblFileToBeUploaded.Text = this.GetLabel("SOM_FileToBeUploaded", true);
            this.btnUpload.Text = this.GetLabel("SOM_Upload");
            this.lblFileUpload.Text = this.GetLabel("SOM_FileToBeUploaded", true);
            this.lblDownloadData.Text = this.GetLabel("SOM_DownloadTheContractorSnapshot");
            this.btnDownTemplte.Text = this.GetLabel("SOM_DownloadEmptyTemplate");
            this.lblUploadNote.Text = this.GetLabel("SOM_Note");
            this.btnDownMTR.Text = this.GetLabel("SOM_DownloadDown");
        }

        void SetLanguage_Click(object sender, EventArgs e)
        {
            SetLanguage();
        }
        #endregion

        protected void btnDownMTR_Click(object sender, System.EventArgs e)
        {
            DataSet ds = MTRC.ODMS.DAL.OPD.CodeSetup.ExternalParty.CQAContractorDownload(this.LangCode);
            if (ds == null)
            {
                return;
            }
            ds.Tables[0].TableName = "CQAContractorStaffSnapshot";

            //------------------------ Modify by zengshunbo 2008-12-11 actionlog(0709) ----------------------

            //--------- add ---------

            ds.Tables[0].Columns[0].ColumnName = this.GetLabel("SOM_ExtPartyStaffID", this.LangCode);
            ds.Tables[0].Columns[1].ColumnName = this.GetLabel("SOM_ExtPartyCode", this.LangCode);
            ds.Tables[0].Columns[2].ColumnName = this.GetLabel("SOM_RespStaffInfoID", this.LangCode);
            ds.Tables[0].Columns[3].ColumnName = this.GetLabel("SOM_RespOrgUnitCode", this.LangCode);
            ds.Tables[0].Columns[4].ColumnName = this.GetLabel("SOM_Contract", this.LangCode);
            ds.Tables[0].Columns[5].ColumnName = this.GetLabel("SOM_StartDate", this.LangCode);
            ds.Tables[0].Columns[6].ColumnName = this.GetLabel("SOM_EndDate", this.LangCode);
            ds.Tables[0].Columns[7].ColumnName = this.GetLabel("COM_Remark", this.LangCode);
            ds.Tables[0].Columns[8].ColumnName = this.GetLabel("SOM_OfficeTel", this.LangCode);
            ds.Tables[0].Columns[9].ColumnName = this.GetLabel("SOM_JobTitle", this.LangCode);
            ds.Tables[0].Columns[10].ColumnName = this.GetLabel("SOM_GradeCode", this.LangCode);
            ds.Tables[0].Columns[11].ColumnName = this.GetLabel("SOM_JobDesc", this.LangCode);
            ds.Tables[0].Columns[12].ColumnName = this.GetLabel("SOM_JobCode", this.LangCode);
            ds.Tables[0].Columns[13].ColumnName = this.GetLabel("SOM_LocationID", this.LangCode);
            ds.Tables[0].Columns[14].ColumnName = this.GetLabel("SOM_FlagObsolete", this.LangCode);

            //----------end add-------

            

            //ds.Tables[0].Columns[5].ColumnName = "StartDate(dd/MM/yyyy)"; 
            //ds.Tables[0].Columns[6].ColumnName = "EndDate(dd/MM/yyyy)";
            //ds.Tables[0].Columns[14].ColumnName = "Valid (Y/N)";

            //------------------------ End modify by zengshunbo 2008-12-11 actionlog(0709) ----------------------

            Common.ExportExcel_showHHMM(ds, Context, "Excel_SOM_CQAContractorStaffSnapshotUpload.xls", "som_upload");
            //this.myExportExcel(ds, Context, "Excel_SOM_CQAContractorStaffSnapshotUpload.xls");
            ds = null;
        }

        protected void btnDownTemplte_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable("CQAContractorStaffSnapshot");

            //-------------------- Modify by zengshunbo 2008-12-11 actionlog(0709) -------------------

            //dt.Columns.Add("ExtPartyStaffID");
            //dt.Columns.Add("ExtPartyCode");
            //dt.Columns.Add("RespStaffInfoID");
            //dt.Columns.Add("RespOrgUnitCode");
            //dt.Columns.Add("ContractID");
            //dt.Columns.Add("StartDate(dd/MM/yyyy)");
            //dt.Columns.Add("EndDate(dd/MM/yyyy)");
            //dt.Columns.Add("Remarks");
            //dt.Columns.Add("OfficePhone");
            //dt.Columns.Add("JobTitle");
            //dt.Columns.Add("GradeCode");
            //dt.Columns.Add("JobDesc");
            //dt.Columns.Add("JobCode");
            //dt.Columns.Add("LocationID");
            ////dt.Columns.Add("FlagCQA");
            //dt.Columns.Add("FlagObsolete(Y/N)");

            dt.Columns.Add(this.GetLabel("SOM_ExtPartyStaffID", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_ExtPartyCode", this.LangCode));

            dt.Columns.Add(this.GetLabel("SOM_RespStaffInfoID", this.LangCode)); ////huangyao
            //dt.Columns.Add(this.GetLabel("SOM_RespStaffInfoID", this.LangCode), Type.GetType("System.String")); ////huangyao

            dt.Columns.Add(this.GetLabel("SOM_RespOrgUnitCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_Contract", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_StartDate", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_EndDate",this.LangCode));
            dt.Columns.Add(this.GetLabel("COM_Remark", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_OfficeTel", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobTitle", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_GradeCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobDesc", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_JobCode", this.LangCode));
            dt.Columns.Add(this.GetLabel("SOM_LocationID", this.LangCode));
            //dt.Columns.Add("FlagCQA");
            dt.Columns.Add(this.GetLabel("SOM_FlagObsolete", this.LangCode));

            //-------------------- End Modify by zengshunbo 2008-12-11 actionlog(0709) ---------------
            //DataRow dr = dt.NewRow();
            //dr[this.GetLabel("SOM_RespStaffInfoID", this.LangCode)] = "string";
            //dt.Rows.Add(dr);

            ds.Tables.Add(dt);

            Common.ExportExcel(ds, Context, "Excel_SOM_CQAContractorStaffSnapshotUpload.xls");
        }




        protected void btnUpload_Click(object sender, System.EventArgs e)
        {
            string fileName = "Excel_SOM_CQAContractorStaffSnapshotUpload.xls";
            bool hasERROR = false;
            if (this.fileUpload.PostedFile.ContentLength <= 0)
            {
                this.Alert(this.GetMessage("SOM_FilePathNull"));
                return;
            }
            //Add by LiangZehong on 2008-12-18, for issue 0634 Begin...
            string fileType = string.Empty;
            fileType = this.fileUpload.PostedFile.FileName.Substring(this.fileUpload.PostedFile.FileName.LastIndexOf(".") + 1);
            if (fileType.ToLower() != ("xls").ToLower())
            {
                this.Alert(this.GetMessage("SOM_FileTypeError"));
                return;
            }
            //End...

            XmlDocument xdoc = new XmlDocument();
            try
            {
                xdoc.Load(this.fileUpload.PostedFile.InputStream);
                //----fixed issue 0633 huangyao modified 20081114
                if (xdoc.GetElementsByTagName("Worksheet").Count > 1)
                {
                    Alert(GetMessage("SOM_OneSheetOnly"));
                    return;
                }
                //------

                //xdoc.Save(@"C:\hrm.xml");			//for debug
                string xpath = "ss:Workbook/ss:Worksheet/ss:Table/ss:Row[not (@ss:Hidden)]";
                System.Xml.XmlNamespaceManager xnm = new XmlNamespaceManager(xdoc.NameTable);

                xnm.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");

                XmlNodeList nodelist = xdoc.SelectNodes(xpath, xnm);
                int i = 0;
                XmlDocument xml = new XmlDocument();
                xml.LoadXml("<root/>");

                foreach (XmlNode node in nodelist)
                {
                    if (i > 0)		// first row is head
                    {
                        string ExtPartyStaffSnapShotID = "",ExtPartyCode = "", RespStaffInfoID = "", RespOrgUnitCode= "", ContractID = "", StartDate = "", EndDate= "", Remarks = "", OfficePhone = "", JobTitle = "",GradeCode="",JobDesc="",JobCode="",LocationID="",FlagObsolete="";
                        try
                        {
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }
                                }
                                switch (k)
                                {
                                    case 0:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            ExtPartyStaffSnapShotID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 1:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            ExtPartyCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 2:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            RespStaffInfoID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 3:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            RespOrgUnitCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 4:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            ContractID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 5:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            StartDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 6:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            EndDate = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 7:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            Remarks = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 8:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            OfficePhone = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                        
                                    case 9:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            JobTitle = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 10:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            GradeCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 11:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            JobDesc = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 12:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            JobCode = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    case 13:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            LocationID = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
                                    //case 14:
                                    //    if (node.ChildNodes[j].ChildNodes[0] != null) FlagCQA = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                    //    break;
                                    case 14:
                                        if (node.ChildNodes[j] != null && node.ChildNodes[j].ChildNodes[0] != null) 
                                            FlagObsolete = node.ChildNodes[j].ChildNodes[0].InnerText.Trim();
                                        break;
  
                                }
                                k++;
                            }
                            //if (ExtPartyStaffSnapShotID == string.Empty || ExtPartyStaffSnapShotID.Length > 40)
                            //    Alert("1");
                            //if (ExtPartyCode == String.Empty || ExtPartyCode.Length > 50)
                            //    Alert("2");
                            //if (FlagObsolete == String.Empty || (FlagObsolete.Trim() != "Y" && FlagObsolete.Trim() != "N"))
                            //    Alert("3");
                            //if (RespStaffInfoID.Length > 6)
                            //    Alert("4");
                            //if (RespOrgUnitCode.Length > 50)
                            //    Alert("5");
                            //if (ContractID.Length > 100)
                            //    Alert("6");
                            //if (Remarks.Length > 60)
                            //    Alert("7");
                            //if (OfficePhone.Length > 30)
                            //    Alert("8");
                            //if (JobTitle.Length > 50)
                            //    Alert("9");
                            //if (GradeCode.Length > 50)
                            //    Alert("10");
                            //if (JobDesc.Length > 30)
                            //    Alert("11");
                            //if (JobCode.Length > 6)
                            //    Alert ("12");

                            if ((ExtPartyStaffSnapShotID == String.Empty) 
                                ||(ExtPartyCode == String.Empty)
                                ||(FlagObsolete == String.Empty)
                                ||(LocationID != String.Empty)
                                ||(ExtPartyStaffSnapShotID.Length>40)
                                ||(ExtPartyCode.Length >50)
                                ||(RespStaffInfoID.Length>6)
                                ||(RespOrgUnitCode.Length>50)
                                ||(ContractID.Length>100)
                                ||(Remarks.Length>60)
                                ||(OfficePhone.Length>30)
                                ||(JobTitle.Length>50)
                                ||(GradeCode.Length>50)
                                ||(JobDesc.Length>30)
                                ||(JobCode.Length>6)
                                ||(FlagObsolete.Trim()!="Y"&&FlagObsolete.Trim()!="N") )
                            {
                                //Alert("error this!");
                                throw new Exception();
                                
                            }
                            

                            // data validation

                            //if (ExtPartyStaffSnapShotID != string.Empty) Convert.ToInt32(ExtPartyStaffSnapShotID);
                            
                            //if (RespStaffInfoID != string.Empty) Convert.ToInt32(RespStaffInfoID);

                            if (StartDate != string.Empty)
                            {
                                //Convert.ToDateTime(StartDate);
                                //DateTime.ParseExact(refreshDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat);
                                DateTime tempdt = DateTime.ParseExact(StartDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat); //huangyao //---huangyao --fixed issue 0663
                            }

                            if (EndDate != string.Empty)
                            {
                                //Convert.ToDateTime(EndDate);
                                DateTime.ParseExact(EndDate, "dd/MM/yyyy", CultureInfo.InvariantCulture.DateTimeFormat); //huangyao //---huangyao --fixed issue 0663
                            }

                            //if (LocationID != string.Empty) Convert.ToInt32(LocationID);
                            //FlagCQA = "true"; Convert.ToBoolean(FlagCQA);
                            if (FlagObsolete != string.Empty)
                            {
                                if (FlagObsolete == "Y")
                                {
                                    FlagObsolete = "false";
                                }
                                else
                                {
                                    FlagObsolete = "true";
                                }
                               Convert.ToBoolean(FlagObsolete );                               
                            }
                        }
                        catch
                        {
                            i++;
                            hasERROR = true;
                            
                            //add by SQB 2008/12/05 begin...
                            APJ.Util.Utility.AddAttribute(node, "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //add by SQB 2008/12/05 end...

                            if (node.ChildNodes[0] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[1] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[2] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[3] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[4] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[4], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[5] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[5], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[6] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[6], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[7] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[8] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[8], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[9] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[9], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[10] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[10], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[11] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[11], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[12] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[12], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[13] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[13], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            if (node.ChildNodes[14] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[14], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //if (node.ChildNodes[15] != null) APJ.Util.Utility.AddAttribute(node.ChildNodes[8], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            continue;
                        }

                        XmlNode xnode = APJ.Util.Utility.AddNode(xml.FirstChild, "row", "");
                        APJ.Util.Utility.AddAttribute(xnode, "idx", i.ToString());
                        APJ.Util.Utility.AddNode(xnode, "ExtPartyStaffSnapShotID", "", ExtPartyStaffSnapShotID);
                        APJ.Util.Utility.AddNode(xnode, "ExtPartyCode", "",ExtPartyCode);
                        APJ.Util.Utility.AddNode(xnode, "RespStaffInfoID", "", RespStaffInfoID);
                        APJ.Util.Utility.AddNode(xnode, "RespOrgUnitCode", "", RespOrgUnitCode);
                        APJ.Util.Utility.AddNode(xnode, "ContractID", "", ContractID);

                        ////APJ.Util.Utility.AddNode(xnode, "StartDate", "", DateTime.Parse(StartDate).ToString("dd/MM/yyyy")); //---huangyao --fixed issue 0663
                        if (StartDate.Trim() != string.Empty)//
                        {
                            StartDate = this.ConvertToDateTime(StartDate).ToString("dd/MM/yyyy");
                        }
                        APJ.Util.Utility.AddNode(xnode, "StartDate", "", StartDate);

                        ////APJ.Util.Utility.AddNode(xnode, "EndDate", "", DateTime.Parse(EndDate).ToString("dd/MM/yyyy")); //---huangyao --fixed issue 0663
                        if (EndDate.Trim() != string.Empty)//
                        {
                            EndDate = this.ConvertToDateTime(EndDate).ToString("dd/MM/yyyy");
                        }
                        APJ.Util.Utility.AddNode(xnode, "EndDate", "", EndDate);
                        ///------------end of huangyao modifed

                        APJ.Util.Utility.AddNode(xnode, "Remarks", "", Remarks);
                        APJ.Util.Utility.AddNode(xnode, "OfficePhone", "", OfficePhone);
                        APJ.Util.Utility.AddNode(xnode, "JobTitle", "", JobTitle);
                        APJ.Util.Utility.AddNode(xnode, "GradeCode", "", GradeCode);
                        APJ.Util.Utility.AddNode(xnode, "JobDesc", "", JobDesc);
                        APJ.Util.Utility.AddNode(xnode, "JobCode", "", JobCode);
                        APJ.Util.Utility.AddNode(xnode, "LocationID", "", LocationID);
                        //APJ.Util.Utility.AddNode(xnode, "FlagCQA", "", FlagCQA);
                        APJ.Util.Utility.AddNode(xnode, "FlagObsolete", "", FlagObsolete);
                    }
                    i++;
                }
                //xml.Save(@"C:\result.xml");
                bool successful = false;
                DataSet dsReturn = DAL.OPD.CodeSetup.ExternalParty.CQAContractorStaffSnapshotUpload(this.ODMUser.ID, this.ODMUser.UserID, this.ODMUser.ActingasUserInfo.ID, this.LangCode, ref successful, xml);
                    //DAL.TCSS.TrainLoadActual.TrainLoadActualUpload(this.ODMUser.ID, this.ODMUser.UserID, this.ODMUser.ActingasUserInfo.ID, this.LangCode, ref successful, xml);

                if (successful)
                {
                    
                    if ((dsReturn.Tables[0].Rows.Count > 0) || (hasERROR))
                    {
                        string stylePath = "ss:Workbook/ss:Styles";
                        string stylePathS22 = "ss:Workbook/ss:Styles/ss:Style[@ss:ID='ODMS_ExportExcel']";
                        XmlNode nodeS22 = xdoc.SelectSingleNode(stylePathS22, xnm);
                        if (nodeS22 == null)
                        {//not exist style ODMS, create dynamically for highlight
                            XmlNode nodeStyles = xdoc.SelectSingleNode(stylePath, xnm);
                            XmlNode nodeStyle = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle, "ID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior = APJ.Util.Utility.AddNode(nodeStyle, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                            XmlNode nodeStyle2 = APJ.Util.Utility.AddNode(nodeStyles, "Style", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeStyle2, "ID", "ODMS2", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            XmlNode nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "Interior", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Color", "#FF0000", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Pattern", "Solid", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            nodeInterior2 = APJ.Util.Utility.AddNode(nodeStyle2, "NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet", "");
                            APJ.Util.Utility.AddAttribute(nodeInterior2, "Format", "General Date", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                        }

                        foreach (DataRow row in dsReturn.Tables[0].Rows)
                        {
                            //huangyao 20081217: fixed issue, let this row all highlight
                            //XmlNode node = nodelist.Item((int)row["idx"]);
                            //APJ.Util.Utility.AddAttribute(node, "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                            //---------------------huangyao----------
                            XmlNode node = nodelist.Item((int)row["idx"]);
                            for (int j = 0, k = 0; j < node.ChildNodes.Count; j++)
                            {
                                if (node.ChildNodes[j].Attributes["ss:Index"] != null)
                                {
                                    int h = 0;
                                    if (int.TryParse(node.ChildNodes[j].Attributes["ss:Index"].Value, out h))
                                    {
                                        k = h - 1;
                                    }

                                    
                                    //for (int m = 0; m < k - j; m++)
                                    //{
                                    //    XmlNode nodeTmp = node.ChildNodes[j].Clone();
                                    //    nodeTmp.Value = "";
                                    //    node.InsertBefore(nodeTmp, node.ChildNodes[j]);
                                    //}
                                }

                                APJ.Util.Utility.AddAttribute(node.ChildNodes[j], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");

                                k++;
                            }
                            //--------------------------------------

                            //---------------------commentted by huangyao----------
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[0], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[1], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[2], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[3], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[4], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[5], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[6], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[7], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[8], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[9], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[10], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[11], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[12], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[13], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            ////APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[14], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //APJ.Util.Utility.AddAttribute(nodelist.Item((int)row["idx"]).ChildNodes[14], "StyleID", "ODMS", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
                            //---------------end------commentted by huangyao----------
                        }
                        Response.Clear();
                        Response.Charset = "UTF-16";
                        Response.ContentEncoding = System.Text.Encoding.GetEncoding(1200);
                        Response.AddHeader("Content-disposition", "attachment;filename=" + fileName);
                        Response.ContentType = "application/vnd.ms-excel";
                        xdoc.Save(Response.OutputStream);
                        xdoc = null;
                        //Alert("Upload file has error record, please checked and upload again!");
                        Response.End();

                    }
                    else
                    {
                        Alert(this.GetMessage("SOM_UpdateSuccessful"));
                    }
                }
                else
                {
                    xdoc = null;
                    //Alert(this.GetMessage("SOM_UpdateError"));
                }
                xml = null;
            }
            catch (Exception ex)
            {
                Alert(this.GetMessage("EstimateUploadUnsuccess"));
                //APJ.Util.Log.WindowLog.Log(ex, APJ.Util.Log.LogType.Error, this.ODMUserID);
            }
            finally
            {
                xdoc = null;
            }

        }
    }
}
